package com.test.demo.controller;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.checkerframework.checker.units.qual.A;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.nio.charset.StandardCharsets;

@RestController
public class ExcelExportController {


    @Resource
    private RedisTemplate redisTemplate;


    @PostMapping (value = "/export/sqlTxt",produces = "application/json;charset=UTF-8")
    public void ExportSqlTxt(@RequestParam("file") MultipartFile file, HttpServletResponse response, HttpServletRequest request) throws IOException, ServletException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());

        XSSFSheet sheet = workbook.getSheetAt(1);


//        ServletOutputStream outputStream = response.getOutputStream();
//        PrintWriter writer = response.getWriter();
        ServletOutputStream outputStream = response.getOutputStream();
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=utf-8");
        response.setHeader("content-disposition", "attachment;filename=" + "sys"+System.currentTimeMillis()+".sql");
//        chain.doFilter(request, response);

//        BufferedWriter bufferedWriter = new BufferedWriter(writer);
        BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);

        int allRows = sheet.getPhysicalNumberOfRows();
//        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 1; i < allRows ; i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFCell cell = row.getCell(3);
            if (cell == null){
                continue;
            }
            String stringCellValue = cell.getStringCellValue();
            if (stringCellValue == null){
                continue;
            }
            if (stringCellValue.equals("启用")){
                CellType cellType = row.getCell(1).getCellType();
                String name = cellType.name();
                String stringCellValue1 = row.getCell(0).getStringCellValue();
                System.out.println(stringCellValue1);
                String code;
                if (name.equals("STRING")){
                    code = row.getCell(1).getStringCellValue();
                }else {
                    double numericCellValue = row.getCell(1).getNumericCellValue();
                    String value = String.valueOf(numericCellValue);
                    String[] split = value.split("\\.");
                    code = split[0];
                }

                String[] split = code.split(",");
                if (split.length > 1){
                    code = split[1];
                }
                StringBuilder append = new StringBuilder().append("INSERT INTO sys_code (code_type, code, code_name, code_alias, remark, other_sign) VALUES('CityCodePKS','")
                        .append(code).append("',").append("'").append(row.getCell(0).getStringCellValue()).append("',null,null,'")
                        .append(row.getCell(5).getStringCellValue()).append("'); \n");
                bufferedOutputStream.write(append.toString().getBytes(StandardCharsets.UTF_8));
//                bufferedOutputStream.write("INSERT INTO sys_code (code_type, code, code_name, code_alias, remark, other_sign) VALUES" +
//                        "('CityCodePKS','" +
//                        code + "','" +
//                        row.getCell(0).getStringCellValue() + "',null,null,'" +
//                        row.getCell(5).getStringCellValue() + "'); \n");

//                bufferedOutputStream.write(append.toString().toCharArray());
                bufferedOutputStream.flush();
            }
        }

        bufferedOutputStream.close();



    }
}
